package com.mison.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mison.model.MisonFeedback;
import com.mison.model.MisonFeedbackPerson;
import com.mison.model.MisonStudent;
import com.mison.utils.DBUtils;
import com.mison.utils.Page;

public class MisonFeedbackDao {

	@SuppressWarnings("unchecked")
	public List<MisonFeedback> getList(Page<MisonFeedback> page) {
		
		
		Connection conn = DBUtils.getConnection();
		
		PreparedStatement ps = null;
		
		ResultSet rs = null;
		
		List<MisonFeedback> list = new ArrayList();
		
		String sql = "SELECT * FROM mison_feedback mf, mison_student ms, mison_feedback_person mfp where mf.student_id = ms.student_id and mf.feedback_person_id=mfp.feedback_person_id limit ?,?";
		
		try {
			ps = conn.prepareStatement(sql);
			
			
			ps.setInt(1, (page.getCurrPage()-1)*page.getPerPage());
			
			ps.setInt(2, page.getPerPage());
			
			System.out.println((page.getCurrPage()-1)*page.getPerPage());
			System.out.println(page.getPerPage());
			System.out.println(ps);
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				
				MisonFeedback m = new MisonFeedback();
				MisonStudent ms = new MisonStudent();
				MisonFeedbackPerson mfp = new  MisonFeedbackPerson();
				
				ms.setStudentId(rs.getInt("student_id"));
				ms.setAccount(rs.getString("account"));
				ms.setMajor(rs.getString("major"));
				ms.setPicPath(rs.getString("pic_path"));
				ms.setPsw(rs.getString("psw"));
				ms.setSchool(rs.getString("school"));
				ms.setStudentName(rs.getString("student_name"));
				mfp.setFeedbackPersonId(rs.getInt("feedback_person_id"));
				mfp.setIsLeave(rs.getInt("is_leave"));
				mfp.setName(rs.getString("name"));
				mfp.setPicPath(rs.getString("pic_path"));
				mfp.setSummary(rs.getString("summary"));
				mfp.setType(rs.getInt("type"));
				m.setMisonStudent(ms);
				m.setMisonFeedbackPerson(mfp);
				
				m.setFeedbackId(rs.getInt("feedback_id"));
				m.setContent(rs.getString("content"));
				m.setTime(rs.getDate("time"));
				list.add(m);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
				
				try {
					rs.close();
					ps.close();
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
			
		return list;
	}

	public int getCount() {

		Connection conn = DBUtils.getConnection();
		
		PreparedStatement ps = null;
		
		ResultSet rs = null;
		
		String sql = "SELECT count(*) FROM mison_feedback mf, mison_student ms, mison_feedback_person mfp where mf.student_id = ms.student_id and mf.feedback_person_id=mfp.feedback_person_id";
		
		try {
			ps = conn.prepareStatement(sql);
			
			rs = ps.executeQuery();
			
			rs.next();
			
			return rs.getInt("count(*)");
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
				
				try {
					rs.close();
					ps.close();
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		return 0;
	}

	public void delete(int id) {
		
		Connection conn = DBUtils.getConnection();
		
		PreparedStatement ps = null;
		
		String sql = "delete from mison_feedback where feedback_id=?";
		
		try {
			ps = conn.prepareStatement(sql);
			
			ps.setInt(1, id);
			
			System.out.println(ps);
			
			ps.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}

	public MisonFeedback updateClass(int id) {

		
		Connection conn = DBUtils.getConnection();
		
		PreparedStatement ps = null;
		
		ResultSet rs = null;
		
		String sql = "select * from mison_feedback where feedback_id=?";
		MisonFeedback m = new MisonFeedback();
		
		try {
			
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			rs.next();	
			m.setFeedbackId(rs.getInt("feedback_id"));
			m.setContent(rs.getString("content"));
			m.setTime(rs.getDate("time"));
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				ps.close();
				conn.close();
			}catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return m;
		
	}

	public void update(MisonFeedback m) {
		Connection conn = DBUtils.getConnection();
		
		PreparedStatement ps = null;
		
		String sql = "update mison_feedback set content=?,time=? where  feedback_id=?";

		try {
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, m.getContent());
			
			ps.setDate(2, new java.sql.Date(m.getTime().getTime()));
			
			ps.setInt(3, m.getFeedbackId());
			
			System.out.println(ps);
			
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}


}
